/**
 * @PROJECT 
 * @DATE 2017-08-23 20:47:34
 * @AUTHOR LUYU
 */
package com.kool.tlwp.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.kool.tlwp.exception.AppException;
import com.kool.tlwp.util.StringUtils;

/**
 * @DESCRIBE 读取excel数据库设计文档并转成建表语句
 * @AUTHOR LUYU
 * @DATE 2017-08-23 20:47:34
 *
 */
public class ExcelReader {
	private String filename;

	/**
	 * @return the filename
	 */
	public String getFilename() {
		return filename;
	}

	/**
	 * @param filename
	 *            the filename to set
	 */
	public void setFilename(String filename) {
		this.filename = filename;
	}

	/**
	 * @param excelInputStream
	 * @param filename
	 */
	public ExcelReader(String filename) {
		super();
		this.filename = filename;
	}

	public List<List<List<String>>> read(int sheetBeg, int sheetEnd) throws AppException {
		Workbook book = null;
		List<List<List<String>>> listSheet = null;
		try {
			File fileExcel = new File(filename);
			FileInputStream excelInputStream = new FileInputStream(fileExcel);
			if (filename.endsWith("xls")) {
				book = new HSSFWorkbook(excelInputStream);
			} else if (filename.endsWith("xlsx")) {
				book = new XSSFWorkbook(excelInputStream);
			}
			listSheet = new ArrayList<List<List<String>>>();
			if (sheetEnd > book.getNumberOfSheets()) {
				sheetEnd = book.getNumberOfSheets();
			}
			for (int i = sheetBeg; i < sheetEnd; i++) {
				List<List<String>> sheet = getSheetValue(book, i, 0, 65535, 0, 65535);
				listSheet.add(sheet);
			}
			book.close();
		} catch (FileNotFoundException e) {
			StringUtils.debug("获取excel错误：" + e.getMessage());
			throw new AppException("获取excel错误");
		} catch (IOException e) {
			StringUtils.debug("获取excel错误：" + e.getMessage());
			throw new AppException("获取excel错误");
		}
		return listSheet;
	}

	/**
	 * 
	 * @DESCRIBE 获取excel的一页数据
	 * @DATE 2017年8月25日 下午9:07:57
	 *
	 * @param book
	 * @param sheetIndex
	 * @param rowBegin
	 * @param rowEnd
	 * @param cellBegin
	 * @param cellEnd
	 * @return
	 * @throws IOException
	 */
	private List<List<String>> getSheetValue(Workbook book, int sheetIndex, int rowBegin, int rowEnd, int cellBegin,
			int cellEnd) throws IOException {
		Sheet sheet = book.getSheetAt(sheetIndex);
		List<List<String>> listRow = new ArrayList<List<String>>();
		if (rowEnd > sheet.getLastRowNum()) {
			rowEnd = sheet.getLastRowNum();
		}
		for (int i = rowBegin; i <= rowEnd; i++) {
			Row row = sheet.getRow(i);
			List<String> listCell = new ArrayList<String>();
			if (cellEnd > row.getLastCellNum()) {
				cellEnd = row.getLastCellNum();
			}
			for (int j = 0; j < cellEnd; j++) {
				Cell cell = row.getCell(j);
				String cellValue = getCellValue(cell);
				listCell.add(cellValue);
			}
			listRow.add(listCell);
		}

		return listRow;
	}

	/**
	 * @DESCRIBE 转单元格数据的格式，暂时只需要String类型
	 * @DATE 2017-08-23 21:35:42
	 *
	 * @param cell
	 * @return
	 */
	private String getCellValue(Cell cell) {
		cell.setCellType(CellType.STRING);
		return String.valueOf(cell.getStringCellValue());
	}

}
